﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class TalkHouseListDAO
    {
        /// <summary>
        /// 新建聊天室信息
        /// </summary>
        /// <param name="thName"></param>
        /// <param name="thLutime"></param>
        /// <returns></returns>
        public int SaveTalkHouseInfo(string thName, string thlWelcome, string thlTheme, DateTime thLutime)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into TalkHouseList values (@thName, @thlWelcome, @thlTheme, @thLutime, default)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@thName", thName));
                sqlCmd.Parameters.Add(new SqlParameter("@thlWelcome", thlWelcome));
                sqlCmd.Parameters.Add(new SqlParameter("@thlTheme", thlTheme));
                sqlCmd.Parameters.Add(new SqlParameter("@thLutime", thLutime));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 查找聊天室信息列表
        /// </summary>
        /// <returns></returns>
        public List<TalkHouseList> findALL()
        {
            List<TalkHouseList> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from TalkHouseList order by TalkHouseId desc";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<TalkHouseList>();
                    while (sqlDr.Read())
                    {
                        TalkHouseList thl = new TalkHouseList();
                        thl.TalkHouseId = (int)sqlDr["TalkHouseId"];
                        thl.TalkHouseName = (string)sqlDr["TalkHouseName"];
                        thl.TalkHouseWelcome = (string)sqlDr["TalkHouseWelcome"];
                        thl.TalkHouseTheme = (string)sqlDr["TalkHouseTheme"];
                        thl.TalkHouseLastUpTime = (DateTime)sqlDr["TalkHouseLastUpTime"];
                        thl.TalkHouseOpenState = (int)sqlDr["TalkHouseOpenState"];
                        list.Add(thl);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据编号查询聊天室信息
        /// </summary>
        /// <param name="thid"></param>
        /// <returns></returns>
        public TalkHouseList findById(int thid)
        {
            TalkHouseList thl = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from TalkHouseList where TalkHouseId = @thid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@thid", thid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    thl = new TalkHouseList();
                    sqlDr.Read();
                    thl.TalkHouseId = (int)sqlDr["TalkHouseId"];
                    thl.TalkHouseName = (string)sqlDr["TalkHouseName"];
                    thl.TalkHouseWelcome = (string)sqlDr["TalkHouseWelcome"];
                    thl.TalkHouseTheme = (string)sqlDr["TalkHouseTheme"];
                    thl.TalkHouseLastUpTime = (DateTime)sqlDr["TalkHouseLastUpTime"];
                    thl.TalkHouseOpenState = (int)sqlDr["TalkHouseOpenState"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return thl;
        }

        /// <summary>
        /// 根据聊天室编号更新聊天室信息
        /// </summary>
        /// <param name="thName"></param>
        /// <param name="thLutime"></param>
        /// <param name="thid"></param>
        /// <returns></returns>
        public int UpdateTalkHouseInfo(string thName, string thWelcome, string thTheme, DateTime thLutime, int thid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update TalkHouseList set TalkHouseName = @thName, TalkHouseWelcome = @thWelcome, TalkHouseTheme = @thTheme, TalkHouseLastUpTime = @thLutime where TalkHouseId = @thid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@thName", thName));
                sqlCmd.Parameters.Add(new SqlParameter("@thWelcome", thWelcome));
                sqlCmd.Parameters.Add(new SqlParameter("@thTheme", thTheme));
                sqlCmd.Parameters.Add(new SqlParameter("@thLutime", thLutime));
                sqlCmd.Parameters.Add(new SqlParameter("@thid", thid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据聊天室编号删除聊天室信息
        /// </summary>
        /// <param name="thid"></param>
        /// <returns></returns>
        public int DelTalkHouseInfoById(int thid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from TalkHouseList where TalkHouseId = @thid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@thid", thid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据聊天室编号更改聊天室开放状态
        /// </summary>
        /// <param name="state"></param>
        /// <param name="thlid"></param>
        /// <returns></returns>
        public int UpdateTalkHouseOpenState(int state, int thlid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update TalkHouseList set TalkHouseOpenState = @state where TalkHouseId = @thlid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@state", state));
                sqlCmd.Parameters.Add(new SqlParameter("@thlid", thlid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }
    }
}
